SQL statements

SQL statements are used heavily in database programming. By using it you can retrieve
filtered data from one table or more than one tables, and you can do any thing in
database programming. SQL statements are database independent. It means that we can
apply the same syntax for DBase tables, Paradox, Access, Oracle and all existed databse
management systems. Also it is programming tool independed. We will find the same
syntax used by Delphi, C++Builder, Oracle,, etc. Inspite of this standardization,
there is more than one SQL dialects, but there is a slight difference between these
dialects.

SQL statements in Delphi can be used in TQuery, and TStoredProc components. There
are two types of SQL statements:

1. Select statements:
Used to retrieve data from database but it didn't modify it.
Example: retrieve a phone table from database:

Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('select * from phone');
Query1.Open;

This kind of Query can be treated as a normal table for example we can use below
statements with this query:

Query1.First;
NumStr:= Query1.FieldByName('Number').AsString;
Query1.Next;

2. Executable statements:
Used to execute command in databases such as deletion, creating tables, modifing
data,,, etc.
Example: Delete records from phone table which it's Number field is empty:

Query1.SQL.Clear;
Query1.SQL.Add('delete from phone where Number=""')
Query1.ExecProc;

This kind of queries cann't be used as a table, so that we can not use such statement:

Query1.FieldByName('Number').AsString...

If you have no experience in SQL syntax, I advice you to practice it in Database
Desktop tool befor using SQL in real life work. This tool really help me alot to
learn SQL syntax in first days.

To open the SQL editor in Database Desktop:
- Click File/New/SQL File
- You need to select a database to work in. You can write the full path of your
tables such as:

select * from "c:\data\phone.db"

But the easiest way is to select Alias. Select SQL/Select Alias from menu then choose
your Alias. If you select alias, then you need only to write table name such as:

select * from phone

Select Statement:

Select statement is the most important keyword in SQL, and it is used to retreive
the data from tables. Here is an examples of using it with different styles:

- select * from phone

Retrieves all table data.

- select Name, Number from phone

Retrieves only two columns from phone: Name and Number

- select Name as Freind, Number as TelNumber from phone

Same as the previous example. The only difference is that it displays Name and Number fields
with Friend and TelNumber. But this only affects the result display rather than the
actual structure field names.

- select * from phone where Name = 'Motaz'

Returns only the records in which Name field contains Motaz.
This match is case sensitive, so that it will not return 'MOTAZ' nor 'motaz'.

- select * from phone where Upper(Name) = 'MOTAZ'

Returns all records containing 'Motaz', 'MOTAZ', 'motaz', or any case of this name. Upper function
converts Name field in all records to uppercase at the time of comparison, but it
didn't affects the case of retreived records.

- select * from exam where Degree > 50 and Subject = 2

- select * from exam where Degree between 90 and 100

The above statements are very clear.

- select * from phone order by Name

- select * from phone order by Name desc

- select * from phone order by Name, Number

The above statements retrieve the table sorted by name. The first one sorted ascending
order and the second descending order. The third one sort the table by name, if there
is duplication in name at some records these records will be sorted by Number field.

- select count(*) from phone

This statement returns the number of records in phone table.

- select count(*) from phone where Number like '47%'

The above statement returns the number of telephones that begin with 47

- select sum(degree) from exam
Returns the total summation of degree field in all records

- select avg(degree) as Average from exam
Returns the average of degree field in the table, and the name of result parameter
will be Average.

- select sum(degree) / count(*) as Average from exam

Returns the same result as the previous example.

- select max(degree) from exam
Returns the maximum value of degree field among all records.

- select min(degree) from exam
Returns the minimum value of degree field among all records.

- select distinct Subject from exam
Without distinct the result will be the entire column of Subject field, but distinct
prevents the repetition of data, so if we have three types of subjects and the table
has 100 records, the result will be only three subjects.

Create table:

create table People (ID AutoInc, Name varchar(30),
DOB Date, Notes BLOB(20))

Creates new table with specified fields name and type

Insert data:

Insert into People(Name, DOB, Notes) values('Mohammed',
'2-Jan-1975','Comments')

Insert data in specified fields

Delete records:

delete from People where Name = ''

Deletes all records that has no data in it's Name field.

Update records:

update People
set Notes = 'Student' where DOB < '1-Jan-1974'

Changes Notes field contents to 'Student' in all people which thier DOB are less
than '1-Jan-1974'